﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace AcessaDadosBD.Repositorios
{
    public class ClinicaHospitalRepository : IRepository<ClinicaHospital>
    {
        private IDbCommand Command { get; set; }

        public ClinicaHospitalRepository(IDbCommand cmd)
        {
            this.Command = cmd;
        }

        /// <summary>
        /// Retorna uma clinica/hospital baseado em seu identificador
        /// </summary>
        /// <param name="id">identificador</param>
        /// <returns></returns>
        public ClinicaHospital GetById(int id)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "SELECT idPrestador,CNES,CNPJ FROM tbClinicaHospital WHERE idPrestador=" + id;
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteReader();

                if (reader.Read())
                {
                    ClinicaHospital clinicaHospital = new ClinicaHospital();
                    clinicaHospital.ID = int.Parse(reader["idPrestador"].ToString());
                    clinicaHospital.CNPJ = reader["CNPJ"].ToString();
                    clinicaHospital.CNES = reader["CNES"].ToString();
                    return clinicaHospital;
                }
                else
                {
                    throw new Exception("Hospital/Clinica não encontrado");
                }
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        /// <summary>
        /// Retorna todos os registros de clinicas e hospitais existentes
        /// </summary>
        /// <returns></returns>
        public IQueryable<ClinicaHospital> GetAll()
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "SELECT idPrestador,CNES,CNPJ FROM tbClinicaHospital";
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteReader();

                List<ClinicaHospital> list = new List<ClinicaHospital>();
                while (reader.Read())
                {
                    ClinicaHospital clinicaHospital = new ClinicaHospital();
                    clinicaHospital.ID = int.Parse(reader["idPrestador"].ToString());
                    clinicaHospital.CNPJ = reader["CNPJ"].ToString();
                    clinicaHospital.CNES = reader["CNES"].ToString();
                    list.Add(clinicaHospital);
                }
                return list.AsQueryable<ClinicaHospital>();
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }
        
        /// <summary>
        /// Insere uma nova clinica/hospital no banco de dados
        /// </summary>
        /// <param name="entity"></param>
        public int Insert(ClinicaHospital entity)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "INSERT INTO tbClinicaHospital (idPrestador, CNPJ, CNES) VALUES (" + entity.ID + ",'" + entity.CNPJ + "', '" + entity.CNES + "')";
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteNonQuery();

                return entity.ID;
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        /// <summary>
        /// Remove uma clinica do banco de dados
        /// </summary>
        /// <param name="entity">Entidade a ser removida</param>
        public void Delete(ClinicaHospital entity)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "DELETE FROM tbClinicaHospital WHERE idPrestador=" + entity.ID;
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        /// <summary>
        /// Atualiza uma clinica/hospital existente no banco
        /// </summary>
        /// <param name="entity">Entidade a ser realizada</param>
        public void Update(ClinicaHospital entity)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "UPDATE tbClinicaHospital SET CNPJ='" + entity.CNPJ + "', CNES='" + entity.CNES + "' WHERE idPrestador=" + entity.ID;
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }
    }
}
